IB/FIB - dynamicky SQL

Otázka od: pilifs

14. 4. 2004 11:34

Zdravim,
lze udelat v IB/FIB procedure neco takoveho (prepsano z MSSQL):
.....

declare @cmd varchar(500)
set @cmd = 'SELECT * FROM Tabulka'
exec (@cmd)

.....
------------------------------------------------------

Procedura nyni vrati dataset.

L. Filip

Odpovedá: Pavel Cisar

14. 4. 2004 11:57

Haj hou1

On 14 Apr 2004 at 12:29, pilifs wrote:

> lze udelat v IB/FIB procedure neco takoveho (prepsano z MSSQL):
> .....
>
> declare @cmd varchar(500)
> set @cmd = 'SELECT * FROM Tabulka'
> exec (@cmd)

Pouze Firebird 1.5, prikaz EXECUTE STATEMENT

S pozdravem
Pavel Cisar ( ICQ: 89017288)
Mobil: 724 281429
http://www.ibphoenix.cz
Vse co potrebujete pro Firebird a InterBase

Odpovedá: pilifs

14. 4. 2004 12:17

Asi jsem to nepochopil:
.....
DECLARE VARIABLE cmd VARCHAR(500);
....
cmd = 'SELECT * FROM Tabluka';
EXECUTE cmd;
.....
-------------------------------------
Takhle asi ne, ze? Porad se mi to nedari.
Nejaky exampl by nebyl?

Diky.

L. Filip,

>
> Haj hou1
>
> On 14 Apr 2004 at 12:29, pilifs wrote:
>
> > lze udelat v IB/FIB procedure neco takoveho (prepsano z MSSQL):
> > .....
> >
> > declare @cmd varchar(500)
> > set @cmd = 'SELECT * FROM Tabulka'
> > exec (@cmd)
>
> Pouze Firebird 1.5, prikaz EXECUTE STATEMENT
>

Odpovedá: Pavel Cisar

14. 4. 2004 12:37

Haj hou!

On 14 Apr 2004 at 12:55, pilifs wrote:

> Asi jsem to nepochopil:
> .....
> DECLARE VARIABLE cmd VARCHAR(500);
> ....
> cmd = 'SELECT * FROM Tabluka';
> EXECUTE cmd;
> .....
> -------------------------------------
> Takhle asi ne, ze? Porad se mi to nedari.
> Nejaky exampl by nebyl?

Coz takhle precist si Release Notes k v1.5 ?

CREATE PROCEDURE DynamicSampleOne (Pname VARCHAR(100))
AS
  DECLARE VARIABLE Sql VARCHAR(1024);
  DECLARE VARIABLE Par INT;
BEGIN
  SELECT MIN(SomeField) FROM SomeTable INTO :Par;
  Sql = ?EXECUTE PROCEDURE ? || Pname || ?(?;
  Sql = Sql || CAST(Par AS VARCHAR(20)) || ?)?;
  EXECUTE STATEMENT Sql;
END

nebo

CREATE PROCEDURE DynamicSampleTwo (TableName VARCHAR(100))
AS
  DECLARE VARIABLE Par INT;
BEGIN
  EXECUTE STATEMENT ?SELECT MAX(CheckField) FROM ? || TableName INTO
  :Par;
  IF (Par > 100) THEN
  EXCEPTION Ex_Overflow ?Overflow in ? || TableName;
END

nebo

CREATE PROCEDURE DynamicSampleThree (
  TextField VARCHAR(100),
  TableName VARCHAR(100))
  RETURNING_VALUES (Line VARCHAR(32000))
AS
  DECLARE VARIABLE OneLine VARCHAR(100);
BEGIN
  Line = ??;
  FOR EXECUTE STATEMENT
  ?SELECT ? || TextField || ? FROM ? || TableName INTO :OneLine
  DO
  IF (OneLine IS NOT NULL) THEN
    Line = Line || OneLine || ? ?;
  SUSPEND;
END

S pozdravem
Pavel Cisar ( ICQ: 89017288)
Mobil: 724 281429
http://www.ibphoenix.cz
Vse co potrebujete pro Firebird a InterBase